package fun.lichuang.data_mill;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 主处理类，使用 Spring Boot 方式读取配置
 */
@Component
public class DealProcessor implements CommandLineRunner {
    private static final Logger logger = LoggerFactory.getLogger(DealProcessor.class);
    @Autowired
    private DataMillConfig config;

    @Override
    public void run(String... args) throws Exception {
        // 允许更高的压缩比，避免 Zip bomb 检测误报
        ZipSecureFile.setMinInflateRatio(0.0001);
        // 读取配置
        String ksDir = config.getKsDir();
        String ksFilename = config.getKsFilename();
        String dataDir = config.getDataDir();
        String cpaFilenamePrefix = config.getCpaFilenamePrefix();
        String outputFilenamePrefix = config.getOutputFilenamePrefix();

        // 3. 获取当天日期字符串
        String nowStr = java.time.LocalDate.now().format(java.time.format.DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        String ksPath = ksDir + "\\" + ksFilename;
        String cpaPath = dataDir + "\\" + cpaFilenamePrefix + nowStr + ".xlsx";
        String outputPath = ksDir + "\\" + outputFilenamePrefix + nowStr + ".xlsx";

        logger.info("快手直签运营报表路径: {}", ksPath);
        logger.info("CPA拉新-LT数据导出路径: {}", cpaPath);
        logger.info("消耗csv目录: {}", dataDir);
        logger.info("输出文件路径: {}", outputPath);

        // 用 EasyExcel 读取快手直签运营报表
        KsRowDataListener listener = new KsRowDataListener();
        EasyExcel.read(ksPath, KsRowData.class, listener).sheet().doRead();
        List<KsRowData> ksData = listener.getDataList();
        logger.info("成功读取快手直签运营报表，共 {} 行数据", ksData.size());

        // 读取 CPA 拉新-LT 数据导出
        CpaRowDataListener cpaListener = new CpaRowDataListener();
        EasyExcel.read(cpaPath, CpaRowData.class, cpaListener).sheet().doRead();
        List<CpaRowData> cpaData = cpaListener.getDataList();
        logger.info("成功读取CPA拉新-LT数据导出，共 {} 行数据", cpaData.size());

        // 构建 CPA 数据的日期+渠道ID索引，便于快速查找
        Map<String, CpaRowData> cpaMap = cpaData.stream()
            .filter(row -> isValidDate(row.getDate()))
            .collect(Collectors.toMap(
                row -> row.getDate() + "_" + row.getChannelId(),
                row -> row,
                (v1, v2) -> v1  // 如果有重复，保留第一个
            ));

        // 回填数据到快手直签运营报表
        for (KsRowData ksRow : ksData) {
            String ksDateStr = ksRow.getDate() == null ? "" : new java.text.SimpleDateFormat("yyyy-MM-dd").format(ksRow.getDate());
            String key = ksDateStr + "_" + ksRow.getChannelId();
            CpaRowData cpaRow = cpaMap.get(key);
            if (cpaRow != null) {
                // 回填 N~T 列到 AB~AH列（T-1(6) ~ T-7(18)），只回填数值，否则填 null
                ksRow.setT1_6(isNumeric(cpaRow.getN_col()) ? Double.valueOf(cpaRow.getN_col()) : null);
                ksRow.setT2_12(isNumeric(cpaRow.getO_col()) ? Double.valueOf(cpaRow.getO_col()) : null);
                ksRow.setT3_14(isNumeric(cpaRow.getP_col()) ? Double.valueOf(cpaRow.getP_col()) : null);
                ksRow.setT4_15(isNumeric(cpaRow.getQ_col()) ? Double.valueOf(cpaRow.getQ_col()) : null);
                ksRow.setT5_16(isNumeric(cpaRow.getR_col()) ? Double.valueOf(cpaRow.getR_col()) : null);
                ksRow.setT6_17(isNumeric(cpaRow.getS_col()) ? Double.valueOf(cpaRow.getS_col()) : null);
                ksRow.setT7_18(isNumeric(cpaRow.getT_col()) ? Double.valueOf(cpaRow.getT_col()) : null);
                // 回填 AC 列到 结算数量(排重）
                ksRow.setSettleCount(isNumeric(cpaRow.getAc_col()) ? Double.valueOf(cpaRow.getAc_col()) : null);
                // 回填 M 列到 回流设备数
                ksRow.setReturnDevice(isNumeric(cpaRow.getM_col()) ? Double.valueOf(cpaRow.getM_col()) : null);
                // 回填 L 列到 换机设备数
                ksRow.setDeviceChange(isNumeric(cpaRow.getL_col()) ? Double.valueOf(cpaRow.getL_col()) : null);
                logger.info("回填成功：日期={}, 渠道ID={}", ksRow.getDate(), ksRow.getChannelId());
            }
        }

        // 处理消耗数据回填（最新日期）
        java.util.Date latestDate = ksData.stream()
            .map(KsRowData::getDate)
            .filter(java.util.Objects::nonNull)
            .max(java.util.Date::compareTo)
            .orElse(null);

        if (latestDate != null) {
            String latestDateStr = new java.text.SimpleDateFormat("yyyyMMdd").format(latestDate);
            // 查找对应的消耗数据文件
            Path csvFile = Files.list(Paths.get(dataDir))
                .filter(p -> p.getFileName().toString().matches("material_new_mcc_report_" + latestDateStr + "-" + latestDateStr + ".*\\.csv"))
                .findFirst()
                .orElse(null);

            if (csvFile != null) {
                logger.info("找到消耗数据文件: {}", csvFile);
                // 读取 CSV 数据
                List<String[]> csvRows = readCsv(csvFile.toString(), "GBK", 6);
                // 处理消耗数据回填
                for (KsRowData ksRow : ksData) {
                    String ksDateStr = ksRow.getDate() == null ? "" : new java.text.SimpleDateFormat("yyyy-MM-dd").format(ksRow.getDate());
                    if (ksDateStr.equals(new java.text.SimpleDateFormat("yyyy-MM-dd").format(latestDate))) {
                        String accountId = ksRow.getAccountId();
                        if (accountId != null && !accountId.isEmpty()) {
                            // 查找匹配的消耗数据
                            String cost = null;
                            for (String[] csvRow : csvRows) {
                                if (csvRow.length > 2 && accountId.equals(csvRow[1])) {
                                    cost = csvRow[2];
                                    break;
                                }
                            }
                            if (cost != null && isNumeric(cost)) {
                                ksRow.setCost(Double.valueOf(cost));
                                logger.info("回填消耗成功：账户ID={}, 消耗={}", accountId, cost);
                            } else {
                                // 二次回填：查找账户维度明细报表
                                String detailPath = dataDir + "/账户维度明细报表" + new java.text.SimpleDateFormat("yyyy-MM-dd").format(latestDate) + ".xlsx";
                                try (java.io.InputStream detailIn = Files.newInputStream(java.nio.file.Paths.get(detailPath))) {
                                    // 读取明细表所有行
                                    List<LinkedHashMap<Integer, String>> detailRows = new ArrayList<>();
                                    EasyExcel.read(detailIn)
                                        .sheet()
                                        .registerReadListener(new AnalysisEventListener<LinkedHashMap<Integer, String>>() {
                                            @Override
                                            public void invoke(LinkedHashMap<Integer, String> data, AnalysisContext context) {
                                                detailRows.add(data);
                                            }
                                            @Override
                                            public void doAfterAllAnalysed(AnalysisContext context) {}
                                        }).doRead();

                                    // 构建账户ID到总消耗的Map（假设B列为账户ID，C列为总消耗）
                                    Map<String, String> accountCostMap = new HashMap<>();
                                    for (LinkedHashMap<Integer, String> row : detailRows) {
                                        String detailAccountId = row.get(1);
                                        String totalCost = row.get(2);
                                        if (detailAccountId != null && !detailAccountId.isEmpty()) {
                                            accountCostMap.put(detailAccountId, totalCost);
                                        }
                                    }

                                    String detailCost = accountCostMap.get(accountId);
                                    if (isNumeric(detailCost)) {
                                        ksRow.setCost(Double.valueOf(detailCost));
                                        logger.info("二次回填消耗：账户ID={}, 总消耗={}", accountId, detailCost);
                                    } else {
                                        ksRow.setCost(null);
                                    }
                                } catch (Exception e) {
                                    logger.error("读取账户维度明细报表失败: {}，错误：{}", detailPath, e.getMessage());
                                    ksRow.setCost(null);
                                }
                            }
                        }
                    }
                }
            }
        }

        // 保存回填后的数据
        EasyExcel.write(outputPath, KsRowData.class)
            .sheet("Sheet1")
            .doWrite(ksData);
        logger.info("回填完成，已保存为: {}", outputPath);
    }

    /**
     * EasyExcel 监听器，收集所有数据
     */
    public static class KsRowDataListener extends AnalysisEventListener<KsRowData> {
        private List<KsRowData> dataList = new ArrayList<>();
        @Override
        public void invoke(KsRowData data, AnalysisContext context) {
            dataList.add(data);
        }
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {}
        public List<KsRowData> getDataList() { return dataList; }
    }

    public static class CpaRowDataListener extends AnalysisEventListener<CpaRowData> {
        private List<CpaRowData> dataList = new ArrayList<>();
        private boolean isFirstRow = true;
        @Override
        public void invoke(CpaRowData data, AnalysisContext context) {
            // 跳过标题后第一行的“总计”
            if (isFirstRow) {
                isFirstRow = false;
                if (data != null && ("总计".equals(data.getChannelId()) || "总计".equals(data.getDate() != null ? data.getDate().toString() : ""))) {
                    return;
                }
            }
            // 跳过渠道ID不是数值的行
            if (!isNumeric(data.getChannelId())) {
                return;
            }
            // 格式化日期时遇到异常自动跳过
            try {
                if (data.getDate() == null) return;
                // 这里不做格式化，留给后续处理
            } catch (Exception e) {
                return;
            }
            dataList.add(data);
        }
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {}
        public List<CpaRowData> getDataList() { return dataList; }
    }

    /**
     * 判断字符串是否为数值
     */
    private static boolean isNumeric(String str) {
        if (str == null) return false;
        try {
            Double.parseDouble(str);
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    /**
     * 读取CSV文件，返回每行的字符串数组
     * @param path 路径
     * @param encoding 编码
     * @param skipRows 跳过前几行
     */
    private static List<String[]> readCsv(String path, String encoding, int skipRows) {
        List<String[]> rows = new ArrayList<>();
        try (BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(path), encoding))) {
            String line;
            int cnt = 0;
            while ((line = br.readLine()) != null) {
                if (cnt++ < skipRows) continue;
                rows.add(line.split(","));
            }
        } catch (IOException e) {
            logger.error("读取CSV失败: {}", path, e);
        }
        return rows;
    }

    private static boolean isValidDate(String dateStr) {
        try {
            LocalDate.parse(dateStr, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            return true;
        } catch (Exception e) {
            return false;
        }
    }
} 